﻿using DotNetCommon.Extensions;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DBUtil;

public partial class DBAccess
{
    #region 表名或标识符分析/解析
    /// <summary>
    /// 标识符引用标记, 示例:
    /// <list type="bullet">
    /// <item>sqlserver: new List&lt;string>{ "[]", "\"\"" }</item>
    /// <item>mysql: new List&lt;string>{ "``", "\"\"" }</item>
    /// </list>
    /// </summary>
    public virtual IReadOnlyList<string> QuotedCharacters { get; }
    /// <summary>
    /// 快速给纯净db对象名添加引用符, 如:
    /// <list type="bullet">
    /// <item>sqlserver: db.AddQuote("t_user") => [t_user]</item>
    /// <item>mysql: db.AddQuote("t_user") => `t_user`</item>
    /// <item>mysql含特殊字符: db.AddQuote("ab`cd'e") => `ab``cd'e`</item>
    /// </list>
    /// </summary>
    /// <remarks>
    /// 注意: 传入的应该是纯净名称, 而不是已经 <c>quoted</c> 后的名称, 如:
    /// <list type="bullet">
    /// <item>mysql: db.AddQuote("t_user.id") => `t_user.id` // 显示不是期望的</item>
    /// <item>sqlserver: db.AddQuote("[t_user]") => [[t_user]] // 显然也不是期望的</item>
    /// </list>
    /// 如果, 有已经 <c>quoted</c> 后的名称, 可以像下面一样:
    /// <code>db.ParseQuotedName("`t_user`.id").FullNameQuoted</code>
    /// </remarks>
    public abstract string AddQuote(string pureName);

    /// <summary>
    /// 快速去除 Quote
    /// </summary>
    public virtual string RemoveQuote(string quotedName) => AnalysisQuotedName(quotedName).FirstOrDefault();

    /// <summary>
    /// 解析给定的对象引用名称, 得出可能得 数据库名、表名以及quote模式, 如:
    /// <list type="bullet">
    /// <item>mysql: db.ParseQuotedName("`testdb`.table1");</item>
    /// <item>sqlserver: db.ParseQuotedName("testdb.dbo.[table1]");</item>
    /// </list>
    /// </summary>
    /// <remarks>注意: 如果传入的 quotedName 为空 则返回null</remarks>
    public abstract TableName ParseQuotedName(string quotedName);

    /// <summary>
    /// 获取新的 <seealso cref="TableName"/> 实例, 后续可以再设置表名、schema名, 如:
    /// <code>
    /// //以sqlserver为例:
    /// var objName = db.CreateTableName();
    /// 
    /// objName.SetName("t_user");
    /// //objName.FullNameQuoted: [t_user]
    /// 
    /// objName.SetSchemaName("dbo");
    /// //objName.FullNameQuoted: [dbo].[t_user]
    /// </code>
    /// </summary>
    public abstract TableName CreateTableName();

    /// <summary>
    /// 从给定的数据库对象引用名称中解析出纯净的 seg名称 列表, 如:
    /// <list type="bullet">
    /// <item>sqlserver: [testdb].[dbo].[test] => ["testdb","dbo","test"]</item>
    /// <item>sqlserver: testdb."test" => ["testdb","test"]</item>
    /// <item>mysql: `testdb`.`test` =>["testdb","test"]</item>
    /// <item>mysql: `testdb`.test =>["testdb","test"]</item>
    /// <item>postgresql: "public".test =>["public","test"]</item>
    /// <item>sqlite: "otherdb".test =>["otherdb","test"]</item>
    /// <item>oracle: "user_hr".test =>["user_hr","test"]</item>
    /// <item>oracle: "user_hr".test@dblink =>["user_hr","test","@","dblink"]</item>
    /// </list>
    /// </summary>
    /// <remarks>
    /// 注意: <br />
    /// 如果传入的 quotedName 为空 则返回空集合<br/>
    /// 另外:解析时自动处理转义符, 如:
    /// <list type="bullet">
    /// <item>mysql: db.AnalysisQuotedName("`ta``ble`");//输出: ["ta`ble]"</item>
    /// </list>
    /// </remarks>
    public abstract IEnumerable<string> AnalysisQuotedName(string quotedName);
    #endregion

    #region 防止sql注入
    //public abstract string EscapeString(string value, bool dealQuote, bool dealDoubleQuote);

    /// <summary>
    /// 将字符串进行转义, 达到可以装到两个双引号之间的目的, 这个主要是用来拼接 JsonPath<br />
    /// 示例: <br />
    /// 假设有一个json对象, 它的属性名里有个双引号, 如: {"na\"me": "john"} 或者 它的属性名里有个反斜杠, 如: {"na\\me": "john"}
    /// 如果想改这个属性的值, 那拼接的sql如下:
    /// <list type="bullet">
    /// <item>mysql(属性名里有双引号): select json_set('{"na\\"me":"john"}','$."na\\"me"','jack')</item>
    /// <item>mysql(属性名里有反斜杠): select json_set('{"na\\\\me":"john"}','$."na\\\\me"','jack')</item>
    /// <item>mysql(属性名里有换行符): select json_set('{"na\\r\\nme":"john"}','$."na\\r\\nme"','jack')</item>
    /// <item>sqlserver(属性名里有双引号): select json_modify('{"na\"me":"john"}','$."na\"me"','jack')</item>
    /// <item>sqlserver(属性名里有反斜杠): select json_modify('{"na\\me":"john"}','$."na\\me"','jack')</item>
    /// <item>sqlserver(属性名里有换行符): select json_modify('{"na\r\nme":"john"}','$."na\r\nme"','jack')</item>
    /// </list>
    /// 从上面可以看出, 其实就是在 双引号 或 反斜杠 或 换行符 前加了个反斜杠(mysql和sqlserver的sql不同主要是外层又有一个单引号, 而mysql和sqlserver对单引号内的处理是不同的)
    /// </summary>
    public virtual string EscapeStringForDoubleQuote(string str)
    {
        if (str.IsNullOrWhiteSpace()) return str;
        StringBuilder sb = null;
        int last = -1;
        for (int i = 0; i < str.Length; i++)
        {
            char c = str[i];
            switch (c)
            {
                //为什么不对 % 和 _ 进行转义?
                //因为只有在 like 下 【\%】才等同于 【%】, 而其他时候 【\%】不会认为是转义的效果
                //所以在like时特殊处理, 如: select replace(replace('%_%','_','\_'),'%','\%')
                case '"':
                case '\\':
                case '\r':
                case '\n':
                    {
                        sb ??= new StringBuilder();
                        sb.Append(str, last + 1, i - (last + 1));
                        if (c == '\r')
                        {
                            sb.Append("\\r");
                        }
                        else if (c == '\n')
                        {
                            sb.Append("\\n");
                        }
                        else
                        {
                            sb.Append('\\').Append(c);
                        }
                        last = i;
                        break;
                    }
                default:
                    continue;
            }
        }
        sb?.Append(str, last + 1, str.Length - (last + 1));
        return sb?.ToString() ?? str;
    }

    /// <summary>
    /// 将字符串进行转义, 达到可以装到两个单引号之间的目的, 如:
    /// <list type="bullet">
    /// <item>mysql: EscapeString("admin'") => "admin\\'"</item>
    /// <item>mysql: EscapeString("ad\r\nmin") => "ad\\r\\nmin"</item>
    /// <item>sqlserver: EscapeString("admin'") => "admin''"</item>
    /// <item>sqlserver: EscapeString("ad\r\nmin") => "ad\r\nmin"</item>
    /// </list>
    /// 应用示例(防sql注入, mysql):
    /// <code>
    /// //原用户表单输入
    /// var name = "admin' -- ";
    /// var pwd = "xxxx"
    /// 
    /// //经过转义保护处理
    /// var escapeName = db.EscapeString(name, true, false);//输出: 【admin\' -- 】
    /// var escapePwd = db.EscapeString(pwd, true, false);//输出: 【xxxx】
    /// 
    /// //拼接sql 查询db
    /// var sql = $"select * from t_user t where t.name = '{escapeName}' and t.pwd = '{escapePwd}'";
    /// //已转义: "select * from t_user t where t.name = 'admin\' -- ' and t.pwd = 'xxxx'"
    /// //没转义: "select * from t_user t where t.name = 'admin' -- ' and t.pwd = 'xxxx'
    /// </code>
    /// </summary>
    /// <remarks>
    /// 注意: 每个数据库的处理都不尽相同, 如:
    /// <list type="bullet">
    /// <item>mysql(当没有开启 NO_BACKSLASH_ESCAPES 时) 要对 单引号、双引号、反斜杠、回车换行符 做处理. 处理方法: 在之前添加一个反斜杠</item>
    /// <item>sqlserver/sqlite/postgresql 只需要对 单引号 做处理. 处理方法: 将一个单引号变成两个单引号</item>
    /// </list>
    /// 另外: 
    /// <list type="bullet">
    /// <item>如果要对表名、列名防注入,需要使用 <seealso cref="AddQuote(string)"/></item>
    /// <item>不要进行二次调用</item>
    /// <item>不会对 % 和 _ 进行转义, 因为其在like和非like模式下表现的不一致</item>
    /// </list>
    /// </remarks>
    public virtual string EscapeString(string str) => str?.Replace("'", "''");

    /// <summary>
    /// 保护属性名称(在外层包裹双引号,里面的内容进行转义)<br />
    /// 使用场景主要是: 在sql中根据属性或key值拼接 jsonpath ，如: 
    /// <code>
    /// //示例1: 字典key值含有双引号字符时
    /// var seg =
    ///     $"""
    ///     select * from t_user t
    ///     where {db.WhereSeg&lt;Dictionary&lt;string, double>>(ext => ext["sc\"ore"] > 60, "t")}
    ///     """;
    /// //输出:
    /// select * from t_user t
    /// where (json_value(t.ext,'$."sc\\"ore"' returning double)) > 60
    /// 
    /// //示例2: 
    /// var seg =
    ///     $"""
    ///     select * from t_user t
    ///     where {db.WhereSeg&lt;Dictionary&lt;string, double>>(ext => ext["sc'ore"] > 60, "t")}
    ///     """;
    /// //输出:
    /// select * from t_user t
    /// where (json_value(t.ext,'$."sc\'ore"' returning double)) > 60
    /// </code>
    /// </summary>
    private string ProtectPropertyName(string memberName)
    {
        if (memberName == null) return memberName;
        return $"\"{EscapeStringForDoubleQuote(memberName)}\"";
    }

    /// <summary>
    /// 保护普通字符串(在外层包裹单引号,里面的内容进行转义)<br />
    /// 相比 <seealso cref="EscapeString(string)"/> 在外层多包裹了一对单引号, 使用示例:
    /// <code>
    /// var name = "admin' -- ";
    /// var pwd = "xxxx"
    /// 
    /// var escapeName = db.ProtectStringToSeg(name);//输出: 【'admin\' -- '】
    /// var escapePwd = db.ProtectStringToSeg(pwd);//输出: 【'xxxx'】
    /// 
    /// var sql = $"select * from t_user t where t.name = {escapeName} and t.pwd = {escapePwd}";
    /// //输出: "select * from t_user t where t.name = 'admin\' -- ' and t.pwd = 'xxxx'"
    /// </code>
    /// </summary>
    public string ProtectStringToSeg(string str)
    {
        if (str == null) return str;
        return $"\'{EscapeString(str)}\'";
    }

    /// <summary>
    /// 根据属性名称得到jsonpath, 如:
    /// <list type="bullet">
    /// <item>【"name"】 => 【'$."name"'】</item>
    /// </list>
    /// </summary>
    /// <remarks>
    /// 注意: 已对属性名称做过转义处理, 可以放心使用
    /// </remarks>
    public string GetJsonPathSegByPropertyName(string propName)
    {
        return ProtectStringToSeg($"$.{ProtectPropertyName(propName)}");
    }
    /// <summary>
    /// 根据属性名称得到jsonpath, 如:
    /// <list type="bullet">
    /// <item> ["ext","age"] => 【'$."ext"."age"'】</item>
    /// </list>
    /// </summary>
    /// <remarks>
    /// 注意: 已对属性名称做过转义处理, 可以放心使用
    /// </remarks>
    public string GetJsonPathSegByPropertyName(IEnumerable<string> propNames)
    {
        var str = propNames.Where(i => i.IsNotNullOrWhiteSpace()).Select(ProtectPropertyName).ToStringSeparated(".");
        return ProtectStringToSeg($"$.{str}");
    }
    #endregion
}
